# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import codecs
import json
import pymysql
import datetime
import traceback
import urllib
import os

class MyspiderPipeline(object):
    def process_item(self, item, spider):
        #with open('data_cn.json', 'a') as f:
        #    jsontext = json.dump(dict(item), f, ensure_ascii=False) + ",\n"
        #    f.write(jsontext.encode("utf-8"))
        if spider.name == 'quyu':
            self.store_area_into_mysql(item)
            pass
        if spider.name == 'quyu1':
            self.update_area_into_mysql(item)
            pass
        if spider.name == 'quyu2':
            self.store_area_into_mysql(item)
            pass
        if spider.name == 'jingqu':
            self.store_sight_into_mysql(item)
            pass
        if spider.name == 'jingqutu':
            self.store_sight_img(item)
            pass
        if spider.name == 'quyutu':
            self.store_area_img(item)
            pass
        if spider.name == 'quyutu1':
            self.store_area_img(item)
            pass
        pass

    def store_area_into_mysql(self, item):
        # 打开数据库连接
        db = pymysql.connect(host='192.168.0.116', user='supper',password='Wnym12345678.',database='wnym',charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "INSERT INTO areainfoes(code, name, description, baseinfo, creationtime) VALUES (%s, %s, %s, %s, %s);"

        try:
            # 执行sql语句
            cursor.execute(sql, [item['code'], item['name'], item['desc'], json.dumps(item['info'], ensure_ascii=False), datetime.datetime.now()])
            # 提交到数据库执行
            db.commit()
        except:
            # Rollback in case there is any error
            print('mysql exception:')
            db.rollback()
        finally:
            cursor.close()

        cursor.close()
        # 关闭数据库连接
        db.close()
        pass

    def update_area_into_mysql(self, item):
        
        # 打开数据库连接
        db = pymysql.connect(host='192.168.0.116', user='supper',password='Wnym12345678.',database='wnym',charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "update areainfoes set description=%s, baseinfo=%s where code=%s;"

        try:
            # 执行sql语句
            cursor.execute(sql, [item['desc'], json.dumps(item['info'], ensure_ascii=False), item['code']])
            # 提交到数据库执行
            db.commit()
            print('####################### '+item['code']+ '-' + item['name'] +' 已更新')
        except:
            # Rollback in case there is any error
            print('mysql exception:')
            db.rollback()
        finally:
            cursor.close()

        cursor.close()
        # 关闭数据库连接
        db.close()
        pass

    def store_sight_into_mysql(self, item):
        # 打开数据库连接
        db = pymysql.connect(host='192.168.0.116', user='supper',password='Wnym12345678.',database='wnym',charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        querycursor = db.cursor()

        # SQL
        insertsql = "INSERT INTO sights(name, countrycode, province, areaname, address, description, info, creationtime, ranking, comments, imgcount) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
        updatesql = "UPDATE sights SET ranking=%s, comments=%s, imgcount=%s WHERE id=%s;"
        querysql = "SELECT id, comments, imgcount FROM sights WHERE name = %s and address = %s;"
        removesql = "DELETE FROM sights WHERE id=%s;"
        updateimgcountsql = "UPDATE sights SET imgcount=%s WHERE id=%s;"

        try:
            querycursor.execute(querysql, [item['name'], item['address']])
            data = querycursor.fetchone()

            if data is None:
                cursor.execute(insertsql, [item['name'], '001', item['province'], item['areaname'], item['address'], item['desc'],
                                           json.dumps(item['info'], ensure_ascii=False), datetime.datetime.now(), item['ranking'], item['comments'], item['imgcount']])
                db.commit()
                print ('############################', 'sight added')

            if not data is None:
                if item['imgcount'] == 0:
                    cursor.execute(updateimgcountsql, [0, data[0]])
                    db.commit()
                    print ('############################' 'img count updated')

        except:
            # Rollback in case there is any error
            print('mysql exception:')
            db.rollback()
        finally:
            cursor.close()

        cursor.close()
        # 关闭数据库连接
        db.close()
        pass

    def store_sight_img(self, item):
        sightname = item['sight']
        imgsrcs = item['imgurls']
        areacode = item['areacode']
        dirname = json.dumps(sightname).replace('\\u', '').replace(' ', '').lower()
        topath = "D:/sight/" + areacode + "/"+ str(dirname).replace("\"", "") + "/"
        isExists=os.path.exists(topath)
        if not isExists:
            os.makedirs(topath)
            pass
        index = 0
        length = 3
        if len(imgsrcs) < length:
            length = len(imgsrcs)
        while index < length:
            filepath = topath + str(index + 1) + '.jpg'
            if os.path.exists(filepath):
                print ('########## skip exist file ##########')

            if not os.path.exists(filepath):
                try:
                    urllib.request.urlretrieve(imgsrcs[index], filepath)
                    print ('########## file downloaded ##########')
                except:
                    print ('@@@@@@@@@@', 'file download failed', '@@@@@@@@@@')
                pass

            index += 1
        pass

    def store_area_img(self, item):
        code = item['code']
        imgsrc = item['imgurl']
        picindex = item['index']
        topath = "D:/area/"+ code + "/"

        try:
            isExists=os.path.exists(topath)
            if not isExists:
                os.makedirs(topath)
            filepath = topath + str(picindex) + ".jpg"

            if os.path.exists(filepath):
                print ('########## skip exist file ##########')

            if not os.path.exists(filepath):
                urllib.request.urlretrieve(imgsrc, filepath)
                print ('########## file downloaded ##########')
        except:
            print ('@@@@@@@@@@', 'file download failed', '@@@@@@@@@@')

        pass